//202102104015柴亚楠
package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.authority.Actor;
import cn.edu.sdjzu.xg.kcsj.domain.authority.ActorAssRole;
import cn.edu.sdjzu.xg.kcsj.domain.authority.Role;
import cn.edu.sdjzu.xg.kcsj.service.ActorService;
import cn.edu.sdjzu.xg.kcsj.service.RoleService;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

public final class ActorAssRoleDao {
    private static ActorAssRoleDao actorAssRoleDao = new ActorAssRoleDao();
    private ActorAssRoleDao() {
    }
    public static ActorAssRoleDao getInstance() {
        if (actorAssRoleDao == null) {
            actorAssRoleDao = new ActorAssRoleDao();
        }
        return actorAssRoleDao;
    }
    public boolean add(Connection connection, ActorAssRole actorAssRole) throws SQLException {
        String sql_insert = "INSERT INTO actorAssRole " +
                "( actor_id, role_id) VALUES (?, ?)";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_insert);
        pstmt_update.setInt(1, actorAssRole.getActor().getId());
        pstmt_update.setInt(2, actorAssRole.getRole().getId());
        int rowAffected = pstmt_update.executeUpdate();
        JdbcHelper.close(null,pstmt_update);
        return rowAffected > 0;
    }

    public ActorAssRole find(Connection connection, Integer id) throws SQLException {
        ActorAssRole desiredActorAssRole = null;
        String sql_select_id = "SELECT * FROM actorAssRole WHERE id = ?";
        PreparedStatement pstmt_select_id =
                connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1, id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if (resultSet_select_id.next()) {
            int actor_id = resultSet_select_id.getInt("actor_id");
            int roleId = resultSet_select_id.getInt("role_id");
            Actor actor = ActorService.getInstance().find(actor_id);
            Role role = RoleService.getInstance().find(roleId);
            desiredActorAssRole = new ActorAssRole(id, actor, role);
        }
        JdbcHelper.close(resultSet_select_id, pstmt_select_id);
        return desiredActorAssRole;
    }

    public Collection<ActorAssRole> findByActorId(Connection connection, Actor actor) throws SQLException {
        Collection<ActorAssRole> desiredActorAssRoles = new TreeSet<ActorAssRole>();
        String sql_select_all = "SELECT * FROM ActorAssRole WHERE actor_id = ?";
        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select_all);
        pstmt_select_all.setInt(1, actor.getId());
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        while (resultSet_select_all.next()) {
            int id = resultSet_select_all.getInt("id");
            int roleId = resultSet_select_all.getInt("role_id");
            Role role = RoleService.getInstance().find(roleId);
            ActorAssRole desiredActorAssRole =
                    new ActorAssRole(id, actor, role);
            desiredActorAssRoles.add(desiredActorAssRole);
        }
        JdbcHelper.close(resultSet_select_all, pstmt_select_all);
        return desiredActorAssRoles;
    }

    public Collection<ActorAssRole> findAll(Connection connection) throws SQLException {
        Collection<ActorAssRole> desiredActorAssRoles = new TreeSet<ActorAssRole>();
        String sql_select_all = "SELECT * FROM ActorAssRole";
        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select_all);
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        while (resultSet_select_all.next()) {
            int id = resultSet_select_all.getInt("id");
            int actor_id = resultSet_select_all.getInt("actor_id");
            int roleId = resultSet_select_all.getInt("role_id");
            Actor actor = ActorService.getInstance().find(actor_id);
            Role role = RoleService.getInstance().find(roleId);
            ActorAssRole desiredActorAssRole =
                    new ActorAssRole(id, actor, role);
            desiredActorAssRoles.add(desiredActorAssRole);
        }
        JdbcHelper.close(resultSet_select_all, pstmt_select_all);
        return desiredActorAssRoles;
    }

    public boolean update(Connection connection, ActorAssRole actorAssRole) throws SQLException {
        String sql_update =
                "UPDATE actorAssRole SET actor_id = ?, role_id = ? " +
                        "WHERE id = ?";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_update);
        pstmt_update.setInt(1, actorAssRole.getActor().getId());
        pstmt_update.setInt(2, actorAssRole.getRole().getId());
        pstmt_update.setInt(3, actorAssRole.getId());
        int rowAffected = pstmt_update.executeUpdate();
        JdbcHelper.close(null,pstmt_update);
        return rowAffected > 0;
    }

    public boolean delete(Connection connection, Integer id) throws SQLException {
        String sql_delete_id = "DELETE FROM actorAssRole WHERE id = ?";
        PreparedStatement pstmt_delete =
                connection.prepareStatement(sql_delete_id);
        pstmt_delete.setInt(1, id);
        int rowAffected = pstmt_delete.executeUpdate();
        JdbcHelper.close(null,pstmt_delete);
        return rowAffected > 0;
    }
}


